﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using HotelMatchUtility.Entity;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using Arch.Framework.Utility;

namespace HotelMatchUtility.Data
{
    public class BookingData
    {
        public static List<HotelEntity> GetDataByCityId(int country, int cityId, List<string> hotelList)
        {
            List<HotelEntity> entityList = new List<HotelEntity>();
            Database db = null;
            db = DatabaseFactory.CreateDatabase("ProductDB_SELECT_pro");
            StringBuilder sql = new StringBuilder();

            sql.Append(@"select 
                            inf.resource as hotel
                            ,inf.HotelCode
                            ,cy.country , cy.countryName ,cy.ename as countryEnname
                            ,ct.city , ct.cityName , ct.eName as cityEName
                            ,inf.HotelName ,inf.HotelEnName
                            ,inf.Address_intl as address
                            ,inf.Telephone as Telephone, ex.ZIPCode
                            , ex.website
                            ,inf.LAT ,inf.LON 
                            ,'' as HotelBelongTo
                            ,hl.Bookable
                            --,dt.DistrictID as BookingDistrictCode
                            --,dt.DistrictEnName
                            --,dt.DistrictName
                            ,z.zone
                            ,z.zoneName
                            ,z2.zoneName as DistrictEnName
                            ,pr.Province
                            ,pr.ProvinceName
                            ,pr.EName as ProvinceEnName
                            from hotelInterfaceDB..hotelInterface_hotelGroupInfo inf(nolock) 
                            left join hotelInterfaceDB..HotelGroupInfoExpend ex(nolock)
                            on inf.hotelGroupInfoId = ex.hotelGroupInfoId
                            left join Productdb..hotel hl (nolock)
                            on inf.resource = hl.hotel 
                            --left join HotelinterfaceDb..HotelInterface_bookingDistrict dt (nolock)
                            --on inf.DistrictCode = dt.DistrictID
                            left join ProductDB..Zone(nolock) z
                            on z.zone = hl.zone
                            left join ProductDB..zoneIntl(nolock) z2
                            on z2.zone = hl.zone
                            left join Productdb..resource rs (Nolock)
                            on hl.hotel = rs.resource
                            left join Productdb..city ct (nolock)
                            on rs.city = ct.city
                            left join Productdb..country cy (nolock)
                            on ct.country = cy.country
                            left join ProductDB..Province pr(nolock)
                            on pr.Province = ct.Province
                            where inf.Groupid = 32
                            --and inf.countryCode = @CountryCode
                            and cy.country = @Country  
                        ");

            if (cityId != 0) sql.Append("  and ct.city = @city ");

            HotelEntity classEntity = null;
            DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());
            dbCommand.CommandTimeout = 9999;
            if (cityId != 0) db.AddInParameter(dbCommand, "@city", DbType.Int32, cityId);
            db.AddInParameter(dbCommand, "@Country", DbType.Int32, country);
            using (IDataReader idr = db.ExecuteReader(dbCommand))
            {
                while (idr.Read())
                {
                    foreach (var hotelId in hotelList)
                    {
                        if (int.Parse(hotelId) == ConvertHelper.ToInt32(idr["Hotel"]))
                        {
                            classEntity = new HotelEntity();

                            //----------------------------------HotelInterfaceDB match----------------------------------
                            classEntity.Hotel = ConvertHelper.ToInt32(idr["Hotel"]);
                            classEntity.HotelCode = ConvertHelper.ToString(idr["HotelCode"]);
                            classEntity.HotelName = ConvertHelper.ToString(idr["HotelName"]);
                            classEntity.HotelEnName = ConvertHelper.ToString(idr["HotelEnName"]);
                            classEntity.Address = ConvertHelper.ToString(idr["Address"]);
                            classEntity.Telephone = ConvertHelper.ToString(idr["Telephone"]);
                            classEntity.ZipCode = ConvertHelper.ToString(idr["ZipCode"]);
                            classEntity.City = ConvertHelper.ToInt32(idr["City"]);
                            classEntity.CityEnName = ConvertHelper.ToString(idr["CityEName"]);
                            classEntity.CityName = ConvertHelper.ToString(idr["CityName"]);
                            classEntity.Country = ConvertHelper.ToInt32(idr["Country"]);
                            classEntity.CountryEnName = ConvertHelper.ToString(idr["CountryEnName"]);
                            classEntity.CountryName = ConvertHelper.ToString(idr["CountryName"]);
                            double latResult = 0;
                            double.TryParse(idr["LAT"].ToString(), out latResult);
                            classEntity.Glat = latResult;
                            double lonResult = 0;
                            double.TryParse(idr["LON"].ToString(), out lonResult);
                            classEntity.Glon = lonResult;
                            classEntity.HotelBelongTo = "";
                            classEntity.HotelType = "Booking";
                            classEntity.Bookable = ConvertHelper.ToString(idr["Bookable"]);
                            classEntity.PriceInfo = "";

                            classEntity.DistrictCodeBookingOrCtrip = ConvertHelper.ToString(idr["zone"].ToString());
                            classEntity.DistrictName = ConvertHelper.ToString(idr["zonename"]);
                            //classEntity.DistrictCodeBookingOrCtrip = ConvertHelper.ToString(idr["BookingDistrictCode"]);
                            classEntity.DistrictEnName = ConvertHelper.ToString(idr["DistrictEnName"]);
                            //classEntity.DistrictName = ConvertHelper.ToString(idr["DistrictName"]);
                            classEntity.Website = ConvertHelper.ToString(idr["Website"]);

                            classEntity.Province = ConvertHelper.ToInt32(idr["Province"]);
                            classEntity.ProvinceEnName = ConvertHelper.ToString(idr["ProvinceEnName"]);
                            classEntity.ProvinceName = ConvertHelper.ToString(idr["ProvinceName"]);
                            //----------------------------------HotelInterfaceDB match----------------------------------

                            entityList.Add(classEntity);
                            break;
                        }
                    }
                }
            }
            return entityList;
        }

        public static List<HotelEntity> GetData(int country, List<int> hotelList)
        {
            List<HotelEntity> entityList = new List<HotelEntity>();
            Database db = null;
            db = DatabaseFactory.CreateDatabase("ProductDB_SELECT_pro");
            StringBuilder sql = new StringBuilder();

            sql.Append(@"select 
                            inf.resource as hotel
                            ,inf.HotelCode
                            ,cy.country , cy.countryName ,cy.ename as countryEnname
                            ,ct.city , ct.cityName , ct.eName as cityEName
                            ,inf.HotelName ,inf.HotelEnName
                            ,inf.Address_intl as address
                            ,inf.Telephone as Telephone, ex.ZIPCode
                            , ex.website
                            ,inf.LAT ,inf.LON 
                            ,'' as HotelBelongTo
                            ,hl.Bookable
                            ,dt.DistrictID as BookingDistrictCode
                            ,dt.DistrictEnName
                            ,dt.DistrictName
                            ,pr.Province
                            ,pr.ProvinceName
                            ,pr.EName as ProvinceEnName
                            from hotelInterfaceDB..hotelInterface_hotelGroupInfo inf(nolock) 
                            left join hotelInterfaceDB..HotelGroupInfoExpend ex(nolock)
                            on inf.hotelGroupInfoId = ex.hotelGroupInfoId
                            left join Productdb..hotel hl (nolock)
                            on inf.resource = hl.hotel 
                            left join HotelinterfaceDb..HotelInterface_bookingDistrict dt (nolock)
                            on inf.DistrictCode = dt.DistrictID
                            left join Productdb..resource rs (Nolock)
                            on hl.hotel = rs.resource
                            left join Productdb..city ct (nolock)
                            on rs.city = ct.city
                            left join Productdb..country cy (nolock)
                            on ct.country = cy.country
                            left join ProductDB..Province pr(nolock)
                            on pr.Province = ct.Province
                            where inf.Groupid = 32
                            --and inf.countryCode = @CountryCode
                            and cy.country = @Country  
                        ");
            
            HotelEntity classEntity = null;
            DbCommand dbCommand = db.GetSqlStringCommand(sql.ToString());
            //db.AddInParameter(dbCommand, "@CountryCode", DbType.String, countryCode);
            db.AddInParameter(dbCommand, "@Country", DbType.Int32, country);
            using (IDataReader idr = db.ExecuteReader(dbCommand))
            {
                while (idr.Read())
                {
                    //foreach (var hotelId in hotelList)
                    //{
                        //if (hotelId == ConvertHelper.ToInt32(idr["Hotel"]))
                        //{
                            classEntity = new HotelEntity();

                            //----------------------------------HotelInterfaceDB match----------------------------------
                            classEntity.Hotel = ConvertHelper.ToInt32(idr["Hotel"]);
                            classEntity.HotelCode = ConvertHelper.ToString(idr["HotelCode"]);
                            classEntity.HotelName = ConvertHelper.ToString(idr["HotelName"]);
                            classEntity.HotelEnName = ConvertHelper.ToString(idr["HotelEnName"]);
                            classEntity.Address = ConvertHelper.ToString(idr["Address"]);
                            classEntity.Telephone = ConvertHelper.ToString(idr["Telephone"]);
                            classEntity.ZipCode = ConvertHelper.ToString(idr["ZipCode"]);
                            classEntity.City = ConvertHelper.ToInt32(idr["City"]);
                            classEntity.CityEnName = ConvertHelper.ToString(idr["CityEName"]);
                            classEntity.CityName = ConvertHelper.ToString(idr["CityName"]);
                            classEntity.Country = ConvertHelper.ToInt32(idr["Country"]);
                            classEntity.CountryEnName = ConvertHelper.ToString(idr["CountryEnName"]);
                            classEntity.CountryName = ConvertHelper.ToString(idr["CountryName"]);
                            double latResult = 0;
                            double.TryParse(idr["LAT"].ToString(), out latResult);
                            classEntity.Glat = latResult;
                            double lonResult = 0;
                            double.TryParse(idr["LON"].ToString(), out lonResult);
                            classEntity.Glon = lonResult;
                            classEntity.HotelBelongTo = "";
                            classEntity.HotelType = "Booking";
                            classEntity.Bookable = ConvertHelper.ToString(idr["Bookable"]);
                            classEntity.PriceInfo = "";

                            classEntity.DistrictCodeBookingOrCtrip = ConvertHelper.ToString(idr["BookingDistrictCode"]);
                            classEntity.DistrictEnName = ConvertHelper.ToString(idr["DistrictEnName"]);
                            classEntity.DistrictName = ConvertHelper.ToString(idr["DistrictName"]);
                            classEntity.Website = ConvertHelper.ToString(idr["Website"]);

                            classEntity.Province = ConvertHelper.ToInt32(idr["Province"]);
                            classEntity.ProvinceEnName = ConvertHelper.ToString(idr["ProvinceEnName"]);
                            classEntity.ProvinceName = ConvertHelper.ToString(idr["ProvinceName"]);
                            //----------------------------------HotelInterfaceDB match----------------------------------

                            entityList.Add(classEntity);
                            //break;
                        }
                    //}
                //}
            }
            return entityList;
        }
    }
}
